package org.clipjoint.dao.impl;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.lang3.StringUtils;
import org.clipjoint.dao.UserAdminDao;
import org.clipjoint.entity.dto.UserDto;
import org.clipjoint.entity.User;
import org.clipjoint.util.DataSourceUtil;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class UserAdminDaoImpl implements UserAdminDao {
    @Override
    public List<User> selectAll(UserDto userDto) throws SQLException {
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
       //动态sql
        String sql="select * from `user` where 1=1";
        List<Object> params=new ArrayList<>();
        if (StringUtils.isNotBlank(userDto.getUsername())){
            sql+=" and username = ?";
            params.add(userDto.getUsername());
        }
        if (StringUtils.isNotBlank(userDto.getEmail())){
            sql+=" and email= ?";
            params.add(userDto.getEmail());
        }if (userDto.getCreateTimeStart()!=null&&userDto.getCreateTimeEnd()!=null){
            sql+=" and create_time>= ? and create_time<=?";
            params.add(userDto.getCreateTimeStart());
            params.add(userDto.getCreateTimeEnd());
        }

        //判断排序参数
        if(StringUtils.isNoneBlank(userDto.getOrderField(),userDto.getOrderType())){
            sql+=" order by "+ userDto.getOrderField()+" "+userDto.getOrderType();
        }
        //分页参数
        if(userDto.getPage()!=null &&userDto.getLimit()!=null){
            sql+=" limit ?,?";
            params.add((userDto.getPage()-1)*userDto.getLimit());
            params.add(userDto.getLimit());

        }

        //查询所有结果
        List<User> result=qr.query(sql,new BeanListHandler<>(User.class),params.toArray());
        return result;
    }

    @Override
    public Long selectCount(UserDto build) throws SQLException {
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
        //动态sql
        String sql="select count(*) from `user` where 1=1";
        List<Object> params=new ArrayList<>();
        if (StringUtils.isNotBlank(build.getUsername())){
            sql+=" and username = ?";
            params.add(build.getUsername());
        }
        if (StringUtils.isNotBlank(build.getEmail())){
            sql+=" and email= ?";
            params.add(build.getEmail());
        }if (build.getCreateTimeStart()!=null&&build.getCreateTimeEnd()!=null){
            sql+=" and create_time>= ? and create_time<=?";
            params.add(build.getCreateTimeStart());
            params.add(build.getCreateTimeEnd());
        }
        //查询一个结果
        Map<String, Object> result = qr.query(sql, new MapHandler(), params.toArray());
        return (Long) result.get("count(*)");
    }

    @Override
    public int insert(User user) throws SQLException {
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
        String sql="insert into `user` (`username`,`password`,`email`,`credit`,`account`,`create_time`) value( ? , ? , ? , ? , ? , ? )";
        int result=qr.update(sql,user.getUsername(),user.getPassword(),user.getEmail(),user.getCredit(),user.getAccount(),user.getCreate_time());
        return result;
    }

    @Override
    public int deleteById(Long id) throws SQLException {
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
        String sql="delete from `user` where `id`= ?";
        int result=qr.update(sql,id);
        return result;
    }

    @Override
    public User selectOne(UserDto userDto) throws SQLException {
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
        //动态sql
        String sql="select * from `user` where 1=1";
        List<Object> params=new ArrayList<>();
        if (userDto.getId()!=null){
            sql+=" and id = ?";
            params.add(userDto.getId());
        }
        if (StringUtils.isNotBlank(userDto.getUsername())){
            sql+=" and username = ?";
            params.add(userDto.getUsername());
        }
        if (StringUtils.isNotBlank(userDto.getEmail())){
            sql+=" and email= ?";
            params.add(userDto.getEmail());

        }

        //查询一个结果
        User result=qr.query(sql,new BeanHandler<>(User.class),params.toArray());
        return result;
    }

    @Override
    public int update(User user) throws SQLException {
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
        String sql="update `user` set `username`= ?, `password`= ? ,`email`= ? ,`credit`=?,`account`=? where `id`= ?";
        int result=qr.update(sql,user.getUsername(),user.getPassword(),user.getEmail(),user.getCredit(),user.getAccount(), user.getId());
        return result;
    }
}
